package com.guo.core.framework.pro;

import cn.hutool.core.lang.Console;
import cn.hutool.db.Db;
import cn.hutool.db.DbUtil;
import cn.hutool.db.Entity;
import org.apache.commons.io.FileUtils;
import org.junit.Test;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.*;

public class TmsTest {


    final String SQL1 = "select count(1) as '用户数' from mobile_mdm_user mmu WHERE delete_flag =0 and user_status =0 and mmu.create_time > ? and mmu.create_time < ?;";
    /**
     * 查询用户数 [startDate,endDate)
     * @throws IOException
     */
    @Test
    public void mobile_mdm_user() throws SQLException, InterruptedException {
        LocalDate startDate = LocalDate.of(2022,1,1);
        LocalDate endDate = LocalDate.of(2022,6,7);

        while(startDate.isBefore(endDate)) {
            LocalDate start =  startDate;
            LocalDate end = startDate.plusDays(1);
            Entity entity = Db.use("ztb_prod").queryOne(SQL1, new Object[]{start, end});
            Console.log(startDate+"\t"+entity.getStr("用户数"));
            startDate = startDate.plusDays(1);
            Thread.sleep(500);
        }

    }

    final String SQL2_1 = "select\n" +
        "\tcount(1) as '次数',\n" +
        "\tcase\n" +
        "\t\twhen operation_type = 1 then '启动'\n" +
        "\t\twhen operation_type = 2 then '登录'\n" +
        "\t\twhen operation_type = 3 then '退出登录'\n" +
        "\t\twhen operation_type = 4 then '手动定位'\n" +
        "\t\twhen operation_type = 5 then '自动定位'\n" +
        "\t\twhen operation_type = 6 then '排队登记'\n" +
        "\t\twhen operation_type = 7 then '取消排队'\n" +
        "\t\twhen operation_type = 8 then '发车'\n" +
        "\t\twhen operation_type = 9 then '抵达'\n" +
        "\t\twhen operation_type = 10 then '明细签收'\n" +
        "\t\twhen operation_type = 11 then '回单上传'\n" +
        "\t\twhen operation_type = 12 then '绑定承运商'\n" +
        "\t\twhen operation_type = 13 then '取消绑定'\n" +
        "\t\twhen operation_type = 14 then '查看任务'\n" +
        "\t\twhen operation_type = 15 then '取消黑名单'\n" +
        "\t\twhen operation_type = 16 then '设置黑名单'\n" +
        "\t\twhen operation_type = 17 then '账号启用'\n" +
        "\t\twhen operation_type = 18 then '账号停用'\n" +
        "\t\twhen operation_type = 19 then '手动打点'\n" +
        "\t\twhen operation_type = 20 then '自动打点'\n" +
        "\t\twhen operation_type = 21 then '排队登记'\n" +
        "\t\twhen operation_type = 22 then '美福登录'\n" +
        "\t\twhen operation_type = 999999 then '未定义'\n" +
        "\t\telse '未知'\n" +
        "\tend as '状态'\n" +
        "from\n" +
        "\tmobile_operation_trace\n" +
        "where\n";

    final String SQL2_2 = "\tcreate_time > ";
    final String SQL2_3 = "\tand create_time < ";
    final String SQL2_4 = "group by\n" + "\toperation_type ;";

    /**
     * 查询操作记录
     */
    @Test
    public void mobile_operation_trace() throws SQLException, InterruptedException {
        // [2022-03-01,2022-05-13]
        // (2022-05-13,2022-06-14)
        LocalDate startDate = LocalDate.of(2022,5,13);
        LocalDate endDate = LocalDate.of(2022,6,14);

        while(startDate.isBefore(endDate)) {
            LocalDate start =  startDate;
            LocalDate end = startDate.plusDays(1);

            String SQL2 = SQL2_1.concat(SQL2_2).concat("'"+start.toString()+"'\n").concat(SQL2_3)
                .concat("'"+end.toString()+"'\n").concat(SQL2_4);
            List<Entity> resultList = Db.use("ztb_prod").query(SQL2);
            StringBuffer s = new StringBuffer();
            s.append(start.toString()+";");
            for (Entity entity : resultList) {
//                    s.append(entity.getOrDefault("状态", 0)).append("\t");
//                    s.append(entity.getOrDefault("状态", 0)).append("\t").append(entity.getOrDefault("次数", 0)).append("\t");
                    s.append(entity.getOrDefault("状态", 0)).append("=").append(entity.getOrDefault("次数", 0)).append(";");

            }
            Console.log(s.toString());
            startDate = startDate.plusDays(1);
            Thread.sleep(500);
        }
    }

    public static final String SRC = "D:\\MyData\\guozl22\\IdeaProjects\\tms-third\\tms-third-service\\src\\main\\resources\\pro\\operationTrace.txt";
    @Test
    public void formatData() throws IOException {
        List<String> list = FileUtils.readLines(new File(SRC), "UTF-8");
        Map<String,Map<String,String>> map = new HashMap<>();
        list.forEach(line -> {
            String[]param = line.split(";");
            Map<String,String> valueMap = new HashMap<>();
            map.put(param[0], valueMap);
            for (int i=1; i<param.length; i++) {
                String[]keyMap = param[i].split("=");
                valueMap.put(keyMap[0],keyMap[1]);
            }
        });

        TreeMap treemap = new TreeMap(map);
        treemap.keySet().stream().forEach(k -> {
            Map<String,String> tempMap = (Map<String, String>) treemap.get(k);
            StringBuffer s = new StringBuffer();
            s.append(k).append("\t");
            s.append(tempMap.getOrDefault("登录", "0")).append("\t")
//                .append(tempMap.getOrDefault("退出登录", "0")).append("\t")
//                .append(tempMap.getOrDefault("手动定位", "0")).append("\t")
//                .append(tempMap.getOrDefault("自动定位", "0")).append("\t")
                .append(tempMap.getOrDefault("排队登记", "0")).append("\t")
                .append(tempMap.getOrDefault("取消排队", "0")).append("\t")
//                .append(tempMap.getOrDefault("发车", "0")).append("\t")
                .append(tempMap.getOrDefault("抵达", "0")).append("\t")
                .append(tempMap.getOrDefault("明细签收", "0")).append("\t")
                .append(tempMap.getOrDefault("回单上传", "0")).append("\t")
//                .append(tempMap.getOrDefault("绑定承运商", "0")).append("\t")
//                .append(tempMap.getOrDefault("取消绑定", "0")).append("\t")
//                .append(tempMap.getOrDefault("查看任务", "0")).append("\t")
//                .append(tempMap.getOrDefault("取消黑名单", "0")).append("\t")
//                .append(tempMap.getOrDefault("设置黑名单", "0")).append("\t")
//                .append(tempMap.getOrDefault("账号启用", "0")).append("\t")
//                .append(tempMap.getOrDefault("账号停用", "0")).append("\t")
                .append(tempMap.getOrDefault("手动打点", "0")).append("\t")
//                .append(tempMap.getOrDefault("自动打点", "0")).append("\t")
                .append(tempMap.getOrDefault("美福登录", "0")).append("\t");
//                .append(tempMap.getOrDefault("未定义","0"));
            Console.log(s);
        });

    }

    public static final String SQL3 = "select count(dispatch_no) as '发车单' from mobile_dispatch_online where create_time > ? and create_time <? and delete_flag =0;";

    @Test
    public void 统计司机每天的发车单() throws SQLException, InterruptedException {
        LocalDate startDate = LocalDate.of(2022,6,1);
        LocalDate endDate = LocalDate.of(2022,7,1);

        while(startDate.isBefore(endDate)) {
            LocalDate start =  startDate;
            LocalDate end = startDate.plusDays(1);
            Entity entity = Db.use("ztb_prod").queryOne(SQL1, new Object[]{start, end});
            double driverCount = entity.getDouble("用户数");

            Entity dispatchCountEntity = Db.use("ztb_prod").queryOne(SQL3, new Object[]{start, end});
            double dispatchCount = dispatchCountEntity.getDouble("发车单");

            Console.log(startDate+"\t"+driverCount+"\t"+dispatchCount);
            startDate = startDate.plusDays(1);
            Thread.sleep(500);
        }

    }

    final String SQL2 = "select last_gps_longitude,last_gps_latitude,last_gps_time,last_gps_addr,dispatch_no , driver_contact_way from mobile_dispatch_online where dispatch_no IN(?)";
    public static final String dispatchNoFile = "D:\\MyData\\guozl22\\IdeaProjects\\tms-third\\tms-third-service\\src\\main\\resources\\pro\\发车单.txt";
    @Test
    public void 查询发车单最新打点数据() throws IOException, SQLException {
        List<String> list = FileUtils.readLines(new File(dispatchNoFile), "UTF-8");
        String dispatchNoArray = String.join(",", list);
//        dispatchNoArray = "'"+dispatchNoArray+"'";
        List<Entity> resultList = Db.use("ztb_uat").query(SQL2, dispatchNoArray);
        resultList.forEach(
            entity -> {
                Console.log(entity);
            }
        );

    }


}
